﻿namespace Nello
{
    using Excel;
    using Nello.Data;
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.OleDb;
    using System.Drawing;
    using System.IO;
    using System.Runtime.InteropServices;
    using System.Text;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;

    public class ExcelUtil
    {
        private static string ExcelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[fullPath];Extended Properties=\"Excel 8.0\"";

        public static bool CanOpen(string fullPath, out Linker linker)
        {
            string cnnstr = ExcelConnectionString.Replace("[fullPath]", fullPath);
            linker = new Linker(cnnstr);
            linker.DbProviderFactory = ClassFactory.GetDbProviderFactory("System.Data.OleDb");
            try
            {
                linker.Open();
                linker.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool CanRetrive(Linker linker, string sql, out System.Data.DataTable dt)
        {
            dt = null;
            linker.Open();
            try
            {
                dt = linker.ExecuteDataTable(sql);
                linker.Close();
                return true;
            }
            catch
            {
                linker.Close();
                return false;
            }
        }

        public static void Export(System.Data.DataTable dt)
        {
            Export(dt, "name.xls");
        }

        public static void Export(System.Data.DataTable dt, string fileName)
        {
            TextWriter tw = GetTextWriterByDataTable(dt);
            HttpResponse response = HttpContext.Current.Response;
            response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8));
            response.ContentType = "application/ms-excel";
            response.Write(tw);
            response.End();
        }

        public static void Export(System.Data.DataTable dt, string filePath, string fileName)
        {
            string fullPath = Path.Combine(filePath, fileName);
            SaveToFile(dt, fullPath);
            FileUtil.DownloadFile(fullPath);
        }

        public static void ExportToExcel(System.Data.DataTable dt)
        {
            try
            {
                try
                {
                    ApplicationClass ExcelApp = new ApplicationClass();
                    Worksheet ExcelSheet = (Worksheet) ExcelApp.Workbooks.Add(1).Worksheets[1];
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        ExcelSheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                    }
                    for (int row = 0; row < dt.Rows.Count; row++)
                    {
                        for (int col = 0; col < dt.Columns.Count; col++)
                        {
                            ExcelSheet.Cells[row + 2, col + 1] = dt.Rows[row][col];
                        }
                    }
                    ExcelApp.Visible = true;
                }
                catch (Exception ex)
                {
                    string error = ex.ToString();
                }
            }
            finally
            {
                GC.Collect();
            }
        }

        private static DataGrid GetDataGridByDataTable(System.Data.DataTable dt, params int[] cols)
        {
            DataGrid dataGrid = new DataGrid();
            dataGrid.DataSource = dt.DefaultView;
            dataGrid.AllowPaging = false;
            dataGrid.HeaderStyle.BackColor = Color.Brown;
            dataGrid.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            dataGrid.HeaderStyle.Font.Bold = true;
            dataGrid.DataBind();
            foreach (DataGridItem item in dataGrid.Items)
            {
                foreach (int col in cols)
                {
                    item.Cells[col].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
                }
            }
            return dataGrid;
        }

        public static void GetDataTableFromString(string tmpHtml)
        {
        }

        public static string GetFirstSheetName(string fullPath)
        {
            System.Data.DataTable dt = GetSchemaTableByExcel(fullPath);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0]["TABLE_NAME"].ToString();
            }
            return "";
        }

        public static System.Data.DataTable GetSchemaTableByExcel(string fullPath)
        {
            OleDbConnection cnn = new OleDbConnection(ExcelConnectionString.Replace("[fullPath]", fullPath));
            cnn.Open();
            System.Data.DataTable dt = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            cnn.Close();
            return dt;
        }

        public static IList GetSheetNames(string fullPath)
        {
            return DataUtil.GetArrayList(GetSchemaTableByExcel(fullPath).Columns["TABLE_NAME"]);
        }

        private static TextWriter GetTextWriterByDataGrid(DataGrid dataGrid)
        {
            StringWriter tw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(tw);
            dataGrid.RenderControl(hw);
            return tw;
        }

        public static TextWriter GetTextWriterByDataTable(System.Data.DataTable dt)
        {
            return GetTextWriterByDataGrid(GetDataGridByDataTable(dt, new int[0]));
        }

        public static System.Data.DataTable Import(string fullPath)
        {
            Linker linker;
            if (CanOpen(fullPath, out linker))
            {
                System.Data.DataTable dt;
                string sql = "SELECT * FROM [Sheet1$]";
                if (CanRetrive(linker, sql, out dt))
                {
                    return dt;
                }
                string tablename = GetFirstSheetName(fullPath);
                sql = "select * from " + tablename;
                if (CanRetrive(linker, sql, out dt))
                {
                    return dt;
                }
            }
            return new System.Data.DataTable("Empty");
        }

        public static void SaveToFile(System.Data.DataTable dt, string fullPath)
        {
            FileUtil.AppendText(fullPath, GetTextWriterByDataTable(dt).ToString());
        }

        public static void UpdateColumnName(System.Data.DataTable dt, IList list)
        {
            int count = Math.Min(dt.Columns.Count, list.Count);
            for (int i = 0; i < count; i++)
            {
                dt.Columns[i].ColumnName = list[i].ToString();
            }
        }
    }
}

